package com.project.jdbc.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.shiro.util.CollectionUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.project.jdbc.dao.dialect.IDialect;
import com.project.jdbc.utils.BaseLogger;
import com.project.jdbc.utils.ClassUtils;
import com.project.jdbc.utils.Finder;
import com.project.jdbc.utils.Page;
import com.project.jdbc.utils.RegexValidateUtils;

public abstract class BaseJdbcDaoImpl extends BaseLogger implements
		IBaseJdbcDao {
	/**
	 * 抽象方法.每个数据库的代理Dao都必须实现.在多库情况下,用于区分底层数据库的连接对象,对数据库进行增删改查.</br>
	 * @return
	 */
	public abstract NamedParameterJdbcTemplate getJdbc();
	/**
	 * 抽象方法.每个数据库的代理Dao都必须实现.在多库情况下,用于区分底层数据库的连接对象,调用数据库的函数和存储过程.</br>
	 * @return
	 */
	public abstract SimpleJdbcCall getJdbcCall();
	/**
	 * 获取数据库方言,Dao 中注入spring bean.</br> 例如mysql的实现是 mysqlDialect. oracle的实现是
	 * @return
	 */
	public abstract IDialect getDialect();
	/**
	 * 是否打印sql语句,默认false
	 * @return
	 */
	public boolean showsql(){
		return false;
	}
	/**
	 * 打印sql
	 * 
	 * @param sql
	 */
	private void logInfoSql(String sql) {
		if (showsql()) {
			System.out.println(sql);
		}
	}
	
	public BaseJdbcDaoImpl() {
	}
	/**
	 * 根据page 和finder对象,拼装返回分页查询的语句
	 * 
	 * @param page
	 * @param finder
	 * @return
	 * @throws Exception
	 */
	private String getPageSql(Page page, Finder finder) throws Exception {
		String sql = finder.getSql();
		String orderSql = finder.getOrderSql();

		Map<String, Object> paramMap = finder.getParams();

		// 查询sql、统计sql不能为空
		if (StringUtils.isBlank(sql)) {
			return null;
		}
		if (RegexValidateUtils.getOrderByIndex(sql) > -1) {
			if (StringUtils.isBlank(orderSql)) {
				orderSql = sql.substring(RegexValidateUtils
						.getOrderByIndex(sql));
				sql = sql.substring(0, RegexValidateUtils.getOrderByIndex(sql));
			}
		} else {
			orderSql = " order by id";
		}

		if (page == null) {
			if (StringUtils.isNotBlank(orderSql))
				return sql + " " + orderSql;
			else
				return sql;
		}

		Integer count = null;

		if (finder.getCountFinder() == null) {
			String countSql = new String(sql);
			int order_int = RegexValidateUtils.getOrderByIndex(countSql);
			if (order_int > 1) {
				countSql = countSql.substring(0, order_int);
			}
			countSql = "SELECT count(*)  frame_row_count FROM (" + countSql
					+ ") temp_frame_noob_table_name WHERE 1=1 ";
			count = getJdbc().queryForInt(countSql, paramMap);
		} else {
			count = queryForObject(finder.getCountFinder(), Integer.class);
		}
		// 记录总行数(区分是否使用占位符)
		if (count == 0) {
			return null;
		} else {
			page.setTotalCount(count);
		}
		return getDialect().getPageSql(sql, orderSql, page);
	}
	public <T> List<T> queryForList(Finder finder, Class<T> clazz)
			throws Exception {
		return queryForList(finder, clazz, null);
	}

	public <T> List<T> queryForListByProc(Finder finder, Class<T> clazz)
			throws Exception {
		String procName = finder.getProcName();
		Map params = finder.getParams();
		if (params != null) {
			return (List<T>) getJdbcCall().withProcedureName(procName).execute(
					clazz, params);
		}

		return (List<T>) getJdbcCall().withProcedureName(procName).execute(
				clazz);
	}

	public <T> T queryForObjectByProc(Finder finder, Class<T> clazz)
			throws Exception {
		T t = null;
		String procName = finder.getProcName();
		if (StringUtils.isEmpty(procName)) {
			return null;
		}
		Map<String, Object> params = finder.getParams();
		try {
			if (params == null) {
				throw new InvalidDataAccessApiUsageException(
						"参数不能为空,大哥,spring jdbc 没有你期望的方法,你可以自己封装一个啊!");

			} else {
				t = (T) getJdbcCall().withProcedureName(procName).execute(
						clazz, params);
			}
		} catch (EmptyResultDataAccessException e) {
			t = null;
		}
		return t;
	}

	public <T> T queryForObjectByByFunction(Finder finder, Class<T> clazz)
			throws Exception {
		String funName = finder.getFunName();
		T t = null;
		if (StringUtils.isEmpty(funName)) {
			return null;
		}
		Map<String, Object> params = finder.getParams();
		try {
			if (params == null) {
				throw new InvalidDataAccessApiUsageException(
						"参数不能为空,大哥,spring jdbc 没有你期望的方法,你可以自己封装一个啊!");

			} else {
				t = getJdbcCall().withFunctionName(funName).executeFunction(
						clazz, params);
			}
		} catch (EmptyResultDataAccessException e) {
			t = null;
		}
		return t;
	}

	public Object queryObjectByFunction(Finder finder) {
		String funName = finder.getFunName();
		Object o = null;
		if (StringUtils.isEmpty(funName)) {
			return null;
		}
		Map<String, Object> params = finder.getParams();
		try {
			if (params == null) {
				throw new InvalidDataAccessApiUsageException(
						"参数不能为空,大哥,spring jdbc 没有你期望的方法,你可以自己封装一个啊!");
			} else {

				return getJdbcCall().withFunctionName(funName).execute(params);
			}
		} catch (EmptyResultDataAccessException e) {
			o = null;
		}
		return o;
	}

	public Map<String, Object> queryObjectByProc(Finder finder) {
		String procName = finder.getProcName();
		Map<String, Object> map = null;
		if (StringUtils.isEmpty(procName)) {
			return null;
		}
		Map<String, Object> params = finder.getParams();
		try {
			if (params == null) {
				throw new InvalidDataAccessApiUsageException("参数不能为空,大哥,spring jdbc 没有你期望的方法,你可以自己封装一个啊!");
			} else {
				map = getJdbcCall().withProcedureName(procName).execute(params);
			}
		} catch (EmptyResultDataAccessException e) {
			map = null;
		}
		return map;
	}

	public List<Map<String, Object>> queryForList(Finder finder)
			throws Exception {
		logInfoSql(finder.getSql());
		return getJdbc().queryForList(finder.getSql(), finder.getParams());
	}

	public List<Map<String, Object>> queryForList(Finder finder, Page page)
			throws Exception {
		String pageSql = getPageSql(page, finder);
		if (pageSql == null)
			return null;
		finder.setPageSql(pageSql);

		// 打印sql
		logInfoSql(pageSql);

		return getJdbc().queryForList(pageSql, finder.getParams());
	}

	public <T> T queryForObject(Finder finder, Class<T> clazz) throws Exception {
		// 打印sql
		logInfoSql(finder.getSql());
		T t = null;
		try {
			if (ClassUtils.isBaseType(clazz)) {
				t = (T) getJdbc().queryForObject(finder.getSql(),
						finder.getParams(), clazz);

			} else {
				t = (T) getJdbc().queryForObject(finder.getSql(),
						finder.getParams(),
						ParameterizedBeanPropertyRowMapper.newInstance(clazz));
			}
		} catch (EmptyResultDataAccessException e) {
			t = null;
		}

		return t;
	}

	public Map<String, Object> queryForObject(Finder finder) throws Exception {
		Map<String, Object> map = null;
		try {
			// 打印sql
			logInfoSql(finder.getSql());
			map = getJdbc().queryForMap(finder.getSql(), finder.getParams());
		} catch (EmptyResultDataAccessException e) {
			map = null;
		}
		return map;
	}

	public Integer update(Finder finder) throws Exception {
		// 打印sql
		logInfoSql(finder.getSql());
		return getJdbc().update(finder.getSql(), finder.getParams());
	}

	public <T> List<T> queryForList(Finder finder, Class<T> clazz, Page page)
			throws Exception {
		String pageSql = getPageSql(page, finder);
		if (pageSql == null)
			return null;
		finder.setPageSql(pageSql);

		// 打印sql
		logInfoSql(pageSql);

		if (ClassUtils.isBaseType(clazz)) {
			if (getDialect().isRowNumber()) {
				return getJdbc().query(pageSql, finder.getParams(),
						new RowNumberSingleColumnRowMapper(clazz));
			} else {
				return getJdbc().queryForList(pageSql, finder.getParams(),
						clazz);
			}
		} else {
			return getJdbc().query(pageSql, finder.getParams(),
					ParameterizedBeanPropertyRowMapper.newInstance(clazz));
		}
	}
	public Integer save(Object obj)throws Exception{
		if(obj==null)
			return 0;
		String table = ClassUtils.getTableName(obj);
		if(StringUtils.isEmpty(table))
			return 0;
		StringBuffer sql = new StringBuffer("INSERT INTO ").append(table).append("(")
				.append(ClassUtils.getEntitySqlFile(obj, true));
		
		StringBuffer valueSql = new StringBuffer(" values(").append(ClassUtils.getEntitySqlFile(obj, false));
		sql.append(") ");
		valueSql.append(")");
		sql.append(valueSql.toString());
		if(showsql())
			System.out.println(sql.toString());
		return getJdbc().update(sql.toString(), ClassUtils.getEntityValue(obj));
	}
	public Object saveEntity(Object obj)throws Exception{
		if(obj==null)
			return null;
		String sql = ClassUtils.insertSql(obj);
		if(showsql())
			System.out.println(sql);
		//判断是主键是否息增
		if(ClassUtils.isIdentity(obj)){
			KeyHolder keyHolder = new GeneratedKeyHolder();
			SqlParameterSource ss = new MapSqlParameterSource(ClassUtils.getEntityValue(obj));
			if(getJdbc().update(sql, ss, keyHolder)>0)
				return keyHolder.getKey().longValue();
			else
				return null;
		}
		String uuid = ClassUtils.autoUUID();
		Map<String, Object> autoMap = ClassUtils.getEntityValue(obj);
		autoMap.put(ClassUtils.getEntityPKName(obj), uuid);
		if(getJdbc().update(sql, autoMap)>0)
			return uuid;
		else
			return null;
	}
	public List<Integer> saveList(List objs)throws Exception{
		return updateORsaveList(objs, true);
	}
	private List<Integer> updateORsaveList(List objs, boolean save)throws Exception{
		if(CollectionUtils.isEmpty(objs))
			return null;
		Map[] maps = new HashMap[objs.size()];
		String sql = null;
		for(int i = 0; i < objs.size(); i++){
			Object obj = objs.get(i);
			if(save)
				sql = ClassUtils.insertSql(obj);
			else
				sql = ClassUtils.updateSql(obj);
			Map map = ClassUtils.getEntityValue(obj);
			if(!ClassUtils.isIdentity(obj)&&save)
				map.put(ClassUtils.getEntityPKName(obj), ClassUtils.autoUUID());
			maps[i] = map;
		}
		int[] batchUpdate = getJdbc().batchUpdate(sql,
				SqlParameterSourceUtils.createBatch(maps));
		List<Integer> updateList = new ArrayList<Integer>();
		if (batchUpdate.length < 1) {
			return updateList;
		}
		for (int i : batchUpdate) {
			updateList.add(i);
		}
		return updateList;
	}
	public List<Integer> updateList(List objs)throws Exception{
		return updateORsaveList(objs, false);
	}
	public Integer update(Object obj)throws Exception{
		if(obj==null)
			return 0;
		String sql = ClassUtils.updateSql(obj);
		if(showsql())
			System.out.println(sql);
		return getJdbc().update(sql, ClassUtils.getEntityValue(obj));
	}
}
